﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Exam.Core.DBUtility;
using System.Data.OleDb;

namespace Exam.Core.DAL {
    public class Master {

        public Exam.Core.Model.Master getMaster(int masterId) {
            string SQL = "SELECT top 1 * FROM Master WHERE MasterID=" + masterId;
            DataTable dt = DbHelperOleDb.Query(SQL).Tables[0];
            Exam.Core.Model.Master model = new Exam.Core.Model.Master();
            if(dt.Rows.Count==1){
                model.MasterID = dt.Rows[0]["MasterID"].ToString().Trim();
                model.MasterName = dt.Rows[0]["MasterName"].ToString().Trim();
                model.MasterPassword = dt.Rows[0]["MasterPassword"].ToString().Trim();
                model.DepartmentID = dt.Rows[0]["DepartmentID"].ToString().Trim();
                model.CourseId = dt.Rows[0]["CourseId"].ToString().Trim();
                //model.DeptList = new Department().getDepartmentAllList(model.DepartmentID.Trim(','));
                //model.CourseList = new Course().getCoursesList(model.CourseId.Trim(','));
            }
            return model;
        }

        public Exam.Core.Model.Master getMasterByName(string uname) {
            string SQL = "SELECT top 1 * FROM Master WHERE MasterName=@MasterName";
            OleDbParameter[] parameters = {
					new OleDbParameter("@MasterName", OleDbType.VarChar,100)};
            parameters[0].Value = uname;
            Exam.Core.Model.Master obj = new Exam.Core.Model.Master();
            using (OleDbDataReader dr = DbHelperOleDb.ExecuteReader(SQL, parameters)) {
                while (dr.Read()) {
                    obj.MasterID = dr.GetInt32(0).ToString().Trim();
                    obj.MasterName = dr.GetString(1).Trim();
                    obj.MasterPassword = dr.GetString(2).Trim();
                    obj.DepartmentID = dr.GetString(3).Trim();
                    obj.CourseId = dr.GetValue(4).ToString().Trim();
                }
            }
            return obj;

            //DataTable dt = DbHelperOleDb.Query(SQL).Tables[0];
            //Exam.Core.Model.Master model = new Exam.Core.Model.Master();
            //if (dt.Rows.Count == 1) {
            //    model.MasterID = dt.Rows[0]["MasterID"].ToString().Trim();
            //    model.MasterName = dt.Rows[0]["MasterName"].ToString().Trim();
            //    model.MasterPassword = dt.Rows[0]["MasterPassword"].ToString().Trim();
            //    model.DepartmentID = dt.Rows[0]["DepartmentID"].ToString().Trim();
            //    model.CourseId = dt.Rows[0]["CourseId"].ToString().Trim();
            //    //model.DeptList = new Department().getDepartmentAllList(model.DepartmentID.Trim(','));
            //    //model.CourseList = new Course().getCoursesList(model.CourseId.Trim(','));
            //}
            //return model;
        }

        //返回所有培训师
        public List<Exam.Core.Model.Master> getMasterAllList() {
            List<Exam.Core.Model.Master> list = new List<Exam.Core.Model.Master>();
            string SQL = "SELECT * FROM Master ORDER BY MasterID DESC";
            DataTable dt = DbHelperOleDb.Query(SQL).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Master obj = new Exam.Core.Model.Master();
                obj.MasterID = dr["MasterID"].ToString().Trim();
                obj.MasterName = dr["MasterName"].ToString().Trim();
                obj.MasterPassword = dr["MasterPassword"].ToString().Trim();
                obj.CourseId = dr["CourseId"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                list.Add(obj);
            }
            //可以加入缓存
            return list;
        }

        public bool Add(string MasterName, string MasterPassword, string DepartmentID, string CourseId) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Master(");
            strSql.Append("MasterName,MasterPassword,DepartmentID,CourseId)");
            strSql.Append(" VALUES (");
            strSql.Append("@MasterName,@MasterPassword,@DepartmentID,@CourseId)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@MasterName", OleDbType.VarChar,200),
					new OleDbParameter("@MasterPassword", OleDbType.VarChar,200),
					new OleDbParameter("@DepartmentID", OleDbType.VarChar,50),
                    new OleDbParameter("@CourseId", OleDbType.VarChar,50)};
            parameters[0].Value = MasterName;
            parameters[1].Value = MasterPassword;
            parameters[2].Value = DepartmentID;
            parameters[3].Value = CourseId;
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;

        }

        public bool Edit(string masterid, string MasterName, string MasterPassword, string DepartmentID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Master SET ");
            strSql.Append("MasterName=@MasterName,");
            strSql.Append("MasterPassword=@MasterPassword,");
            strSql.Append("DepartmentID=@DepartmentID ");
            strSql.Append(" WHERE MasterID=@MasterID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@MasterName", OleDbType.VarChar,200),
					new OleDbParameter("@MasterPassword", OleDbType.VarChar,200),
					new OleDbParameter("@DepartmentID", OleDbType.VarChar,50),
                    new OleDbParameter("@MasterID", OleDbType.Integer,4)
                };
            parameters[0].Value = MasterName.Trim();
            parameters[1].Value = MasterPassword.Trim();
            parameters[2].Value = DepartmentID.Trim();
            parameters[3].Value = masterid.Trim();
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;
        }

        public bool Del(int id) {
            bool result = false;
            string SQL = "DELETE FROM Master WHERE MasterID =" + id + "";
            try {
                DbHelperOleDb.ExecuteSql(SQL);
                result = true;
            }
            catch {
                result = false;
            }

            return result;
        }

        public bool EditDept(string mydeptid, string masterid) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Master SET ");
            strSql.Append("DepartmentID=@DepartmentID ");
            strSql.Append(" WHERE MasterID=@MasterID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@DepartmentID", OleDbType.VarChar,200),
                    new OleDbParameter("@MasterID", OleDbType.Integer,4)
                };
            parameters[0].Value = mydeptid.Trim();
            parameters[1].Value = int.Parse(masterid.Trim());
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;
        }
    }
}
